package cn.xdd.dao;

import cn.xdd.dbutil.DbUtil;
import cn.xdd.entity.Book;
import cn.xdd.entity.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.logging.Handler;

/**
 * @author user_xdd
 * @Date 2022-04-02 13:49
 * @description
 * @return
 */
public class BookDao {
        private static  final Logger LOGGER = Logger.getLogger(BookDao.class);

    public List<Book> getallBook() throws SQLException {

        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
        String sql = "SELECT\n" +
                "\tbook.*\n" +
                "FROM\n" +
                "\tbook ";

        List<Book> query = queryRunner.query(conn, sql, new BeanListHandler<>(Book.class));

        conn.close();
        return query;
    }
    public List<Book> getRecommend() throws SQLException {

        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
        String sql = "SELECT\n" +
                "\tbook.*\n" +
                "FROM\n" +
                "\tbook\n" +
                "LIMIT 12";

        List<Book> query = queryRunner.query(conn, sql, new BeanListHandler<>(Book.class));
//        LOGGER.debug("    query:"+query);
        conn.close();
        return query;
    }
    public List<Book> getlimi(int begin,int step) throws SQLException {

        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from book limit ?,?;";

        List<Book> query = queryRunner.query(conn, sql, new BeanListHandler<>(Book.class),begin,step);
//        LOGGER.debug("    query:"+query);
        conn.close();
        return query;
    }
    public List<Book> findBook(String search) throws SQLException {

        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
/*
        String sql = "SELECT\n" +
                "    book.*\n" +
                "FROM\n" +
                "    book\n" +
                "WHERE\n" +
                "        book.book_name LIKE \'%?%\' OR\n" +
                "        book.book_author LIKE \'%?%\'  OR\n" +
                "        book.book_commit LIKE \'%?%\' \n" +
                "LIMIT 0, 100\n";
*/
        String sql = "SELECT\n" +
                "    book.*\n" +
                "FROM\n" +
                "    book\n" +
                "WHERE\n" +
                "        book.book_name LIKE ? OR\n" +
                "        book.book_author LIKE  ? OR\n" +
                "        book.book_commit LIKE ? \n" +
                "LIMIT 0, 100\n";
        
        search='%'+search+'%';
         LOGGER.debug("    search:"+search);
        List<Book> query = queryRunner.query(conn, sql, new BeanListHandler<>(Book.class),search,search,search);
//        LOGGER.debug("    query:"+query);
        conn.close();
        return query;
        //------------------------------- -------------------------------
    }
    public Book getBookByid(Integer id) throws SQLException {

        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
        String sql = "SELECT\n" +
                "\tbook.*\n" +
                "FROM\n" +
                "\tbook\n" +
                "WHERE\n" +
                "\tbook.book_id = ?";
        Book query = queryRunner.query(conn, sql, new BeanHandler<>(Book.class),id);
        LOGGER.debug("    query:"+query);
        conn.close();
        return query;
    }
    public int insertUUid(String uuid, int id) throws SQLException {

        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
        String sql ="update book set book_address = ? where book_id=?";
        int update = queryRunner.update(conn, sql, uuid, id);
         LOGGER.debug("    sql:"+sql);
        conn.close();
        return update;

    }
    public int getbookNums() throws SQLException {

        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
        String sql ="select count(*) as book_id from book b";
        Book book = queryRunner.query(conn, sql,new BeanHandler<>(Book.class));
        int count=book.getBook_id();

       
        conn.close();
        return count;
    }

}
